package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.OpenArea;
import cn.edu.sdjzu.xg.bysj.service.GraduateProjectCategoryService;
import cn.edu.sdjzu.xg.bysj.service.SchoolService;
import cn.edu.sdjzu.xg.bysj.service.TeacherService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class OpenAreaDao {
    private static OpenAreaDao openAreaDao = new OpenAreaDao();
    private OpenAreaDao() {
    }
    public static OpenAreaDao getInstance() {
        return openAreaDao;
    }

    public static Collection<OpenArea> findAll(Connection connection , List conditions_json_str, Pagination pagination) throws SQLException {
        Collection<OpenArea> openAreas = new TreeSet<OpenArea>();
        int totalNum = CommonDao.getInstance().count(connection,"OpenArea");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM OpenArea ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditions_json_str!=null){
            String whereClause = Condition.toWhereClause(conditions_json_str);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }

        System.out.println(sql_select.toString());
        //获得查询的预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加到集合中
        while(resultSet.next()){
            openAreas.add(new OpenArea(resultSet.getInt("id"),
                    resultSet.getString("area"),
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("GPC_id")),
                            TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    resultSet.getString("requirement"),
                    resultSet.getInt("positionNum"),
                    resultSet.getInt("year")));
        }
        JdbcHelper.close(resultSet,pstmt);
        return openAreas;
    }

    public static OpenArea find(int id, Connection connection) throws SQLException {
        //创建sql语句
        String find_sql = "select * from OpenArea where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        OpenArea openArea = new  OpenArea(resultSet.getInt("id"),
                resultSet.getString("area"),
                GraduateProjectCategoryService.getInstance().find(resultSet.getInt("GPC_id")),
                TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                resultSet.getString("requirement"),
                resultSet.getInt("positionNum"),
                resultSet.getInt("year"));
        JdbcHelper.close(resultSet,pstmt);
        return openArea;
    }

    public static boolean update(OpenArea openArea,Connection connection) throws SQLException {

        //创建sql语句
        String update_sql = "update OpenArea set area=?,GPC_id=?,teacher_id=? ,requirement=?,positionNum=?,year=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setString(1,openArea.getArea());
        pstmt.setInt(2,openArea.getGraduateProjectCategory().getId());
        pstmt.setInt(3,openArea.getTeacher().getId());
        pstmt.setString(4,openArea.getRequirement());
        pstmt.setInt(5,openArea.getPositionNum());
        pstmt.setInt(6,openArea.getYear());
        pstmt.setInt(7,openArea.getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            JdbcHelper.close(null,pstmt);
            return false;
        }else
            JdbcHelper.close(null,pstmt);
            return  true;
    }

    public static boolean add(OpenArea openArea, Connection connection) throws SQLException {

        //创建sql语句
        String add_sql =
                "insert into OpenArea (area,GPC_id,teacher_id ,requirement,positionNum,year) values " +
                        "(?,?,?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(add_sql);
        //为预编译参数赋值
        pstmt.setString(1,openArea.getArea());
        pstmt.setInt(2,openArea.getGraduateProjectCategory().getId());
        pstmt.setInt(3,openArea.getTeacher().getId());
        pstmt.setString(4,openArea.getRequirement());
        pstmt.setInt(5,openArea.getPositionNum());
        pstmt.setInt(6,openArea.getYear());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            JdbcHelper.close(null,pstmt);
            return true;
        } else
            JdbcHelper.close(null,pstmt);
            return false;
    }

    public static boolean delete(int id, Connection connection) throws SQLException{

        //创建sql语句
        String delete_sql = "DELETE FROM OpenArea WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(delete_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);

        if (pstmt.executeLargeUpdate() == 1){
            //关闭连接
            JdbcHelper.close(null,pstmt);
            return true;
        }else {
            //关闭连接
            JdbcHelper.close(null,pstmt);
            return false;
        }
    }
    public boolean delete(OpenArea openArea, Connection connection) throws SQLException {

        String delete_sql = "delete from OpenArea  where requirement = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(delete_sql);
        //为预编译参数赋值
        pstmt.setString(1, openArea.getRequirement());
        //根据id删除 并判断是否删除成功
        if (pstmt.executeUpdate() == 0) {
            JdbcHelper.close(null,pstmt);
            return false;
        } else {
            JdbcHelper.close(null,pstmt);
            return true;
        }
    }
}
